/**
 * 数据库初始化脚本
 * 用于创建数据库和表结构
 * 
 * 使用方法: node scripts/initDatabase.js
 */

const { sequelize, testConnection } = require('../config/database');
const User = require('../models/User');
const Category = require('../models/Category');
const Fruit = require('../models/Fruit');
const Warehouse = require('../models/Warehouse');
const FruitWarehouse = require('../models/FruitWarehouse');

const initDatabase = async () => {
  console.log('🔄 Starting database initialization...\n');
  
  try {
    // 1. 测试数据库连接
    console.log('Step 1/5: Testing database connection...');
    const isConnected = await testConnection();
    
    if (!isConnected) {
      console.error('❌ Database connection failed, please check configuration');
      process.exit(1);
    }
    
    // 2. 删除并重新创建所有表（慎用！会清空所有数据）
    console.log('\nStep 2/5: Creating database tables...');
    console.log('⚠️  Warning: This will delete all existing data');
    
    // 先删除所有外键约束
    await sequelize.query('SET FOREIGN_KEY_CHECKS = 0');
    await sequelize.sync({ force: true });
    await sequelize.query('SET FOREIGN_KEY_CHECKS = 1');
    
    console.log('✅ Database tables created successfully');
    
    // 3. 创建测试用户
    console.log('\nStep 3/5: Creating test user...');
    
    await User.create({
      name: 'Administrator',
      username: 'admin',
      password: 'admin123'
    });
    
    console.log('✅ Test user created successfully');
    console.log('   Name: Administrator');
    console.log('   Username: admin');
    console.log('   Password: admin123');
    
    // 4. 创建测试分类数据
    console.log('\nStep 4/7: Creating test category data...');
    
    const testCategories = [
      { name: 'Tropical Fruits', description: '热带水果', icon: '🌴' },
      { name: 'Citrus Fruits', description: '柑橘类水果', icon: '🍊' },
      { name: 'Berries', description: '浆果类', icon: '🫐' },
      { name: 'Stone Fruits', description: '核果类', icon: '🍑' }
    ];
    
    await Category.bulkCreate(testCategories);
    
    console.log(`✅ Successfully created ${testCategories.length} test categories`);
    
    // 5. 创建测试仓库数据
    console.log('\nStep 5/7: Creating test warehouse data...');
    
    const testWarehouses = [
      { 
        name: 'Main Warehouse', 
        location: '123 Main St, New York, NY 10001', 
        capacity: 1000, 
        manager: 'John Smith', 
        phone: '+1-555-0101',
        status: 'active' 
      },
      { 
        name: 'Cold Storage A', 
        location: '456 Oak Ave, Brooklyn, NY 11201', 
        capacity: 500, 
        manager: 'Emily Johnson', 
        phone: '+1-555-0102',
        status: 'active' 
      },
      { 
        name: 'Cold Storage B', 
        location: '789 Pine Rd, Queens, NY 11354', 
        capacity: 500, 
        manager: 'Michael Davis', 
        phone: '+1-555-0103',
        status: 'active' 
      },
      { 
        name: 'Distribution Center', 
        location: '321 Elm St, Manhattan, NY 10013', 
        capacity: 800, 
        manager: 'Sarah Wilson', 
        phone: '+1-555-0104',
        status: 'active' 
      },
      { 
        name: 'Regional Hub', 
        location: '654 Maple Dr, Bronx, NY 10451', 
        capacity: 1200, 
        manager: 'David Brown', 
        phone: '+1-555-0105',
        status: 'maintenance' 
      }
    ];
    
    await Warehouse.bulkCreate(testWarehouses);
    
    console.log(`✅ Successfully created ${testWarehouses.length} test warehouses`);
    
    // 6. 创建测试水果数据
    console.log('\nStep 6/7: Creating test fruit data...');
    
    // 获取创建的仓库
    const warehouses = await Warehouse.findAll();
    const warehouseMap = {};
    warehouses.forEach(w => {
      warehouseMap[w.name] = w.id;
    });
    
    const testFruits = [
      { name: 'Apple', category: 'Stone Fruits', price: 2.50, total_stock: 300, stock: 300 },      // 总库存: 300
      { name: 'Orange', category: 'Citrus Fruits', price: 1.80, total_stock: 200, stock: 200 },    // 总库存: 200
      { name: 'Banana', category: 'Tropical Fruits', price: 1.20, total_stock: 400, stock: 400 },  // 总库存: 400
      { name: 'Strawberry', category: 'Berries', price: 4.50, total_stock: 100, stock: 100 },      // 总库存: 100
      { name: 'Mango', category: 'Tropical Fruits', price: 3.80, total_stock: 50, stock: 50 },     // 总库存: 50
      { name: 'Blueberry', category: 'Berries', price: 5.20, total_stock: 80, stock: 80 },         // 总库存: 80
      { name: 'Watermelon', category: 'Tropical Fruits', price: 6.00, total_stock: 150, stock: 150 }, // 总库存: 150
      { name: 'Grape', category: 'Berries', price: 3.50, total_stock: 200, stock: 200 }             // 总库存: 200
    ];
    
    const createdFruits = await Fruit.bulkCreate(testFruits);
    
    console.log(`✅ Successfully created ${testFruits.length} test fruits`);
    
    // 7. 创建水果-仓库关联数据
    console.log('\nStep 7/7: Creating fruit-warehouse associations...');
    
    const fruitWarehouseData = [
      // Apple 存储在 Main Warehouse 和 Cold Storage A
      { fruit_id: createdFruits[0].id, warehouse_id: warehouseMap['Main Warehouse'], quantity: 80 },
      { fruit_id: createdFruits[0].id, warehouse_id: warehouseMap['Cold Storage A'], quantity: 40 },
      
      // Orange 存储在 Cold Storage A
      { fruit_id: createdFruits[1].id, warehouse_id: warehouseMap['Cold Storage A'], quantity: 85 },
      
      // Banana 存储在 Main Warehouse
      { fruit_id: createdFruits[2].id, warehouse_id: warehouseMap['Main Warehouse'], quantity: 200 },
      
      // Strawberry 存储在 Cold Storage B
      { fruit_id: createdFruits[3].id, warehouse_id: warehouseMap['Cold Storage B'], quantity: 45 },
      
      // Mango 存储在 Cold Storage A 和 Cold Storage B
      { fruit_id: createdFruits[4].id, warehouse_id: warehouseMap['Cold Storage A'], quantity: 10 },
      { fruit_id: createdFruits[4].id, warehouse_id: warehouseMap['Cold Storage B'], quantity: 5 },
      
      // Blueberry 存储在 Cold Storage B
      { fruit_id: createdFruits[5].id, warehouse_id: warehouseMap['Cold Storage B'], quantity: 28 },
      
      // Watermelon 存储在 Main Warehouse 和 Distribution Center
      { fruit_id: createdFruits[6].id, warehouse_id: warehouseMap['Main Warehouse'], quantity: 30 },
      { fruit_id: createdFruits[6].id, warehouse_id: warehouseMap['Distribution Center'], quantity: 20 },
      
      // Grape 存储在 Cold Storage A
      { fruit_id: createdFruits[7].id, warehouse_id: warehouseMap['Cold Storage A'], quantity: 75 }
    ];
    
    await FruitWarehouse.bulkCreate(fruitWarehouseData);
    
    console.log(`✅ Successfully created ${fruitWarehouseData.length} fruit-warehouse associations`);
    
    // 更新可用库存（总库存 - 已分配数量）
    console.log('\n📦 Updating fruit available stocks...');
    for (const fruit of createdFruits) {
      const allocatedQuantity = fruitWarehouseData
        .filter(fw => fw.fruit_id === fruit.id)
        .reduce((sum, fw) => sum + fw.quantity, 0);
      
      const availableStock = fruit.total_stock - allocatedQuantity;
      
      await fruit.update({
        stock: availableStock
      });
      
      console.log(`   ${fruit.name}: Total ${fruit.total_stock}, Allocated ${allocatedQuantity}, Available ${availableStock}`);
    }
    
    // 显示每个仓库的水果数量
    console.log('\n📊 Warehouse fruit distribution:');
    for (const warehouse of warehouses) {
      const count = fruitWarehouseData.filter(fw => fw.warehouse_id === warehouse.id).length;
      const totalQuantity = fruitWarehouseData
        .filter(fw => fw.warehouse_id === warehouse.id)
        .reduce((sum, fw) => sum + fw.quantity, 0);
      console.log(`   ${warehouse.name}: ${count} types, ${totalQuantity} total items`);
    }
    
    console.log('\n✨ Database initialization completed!');
    console.log('📌 You can now start the server: npm start\n');
    
    process.exit(0);
  } catch (error) {
    console.error('\n❌ Initialization failed:', error.message);
    console.error('Detailed error:', error);
    process.exit(1);
  }
};

// 执行初始化
initDatabase();
